package com.neuosft.his.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;

import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

public class BaseDao {
	public static final String DriverName = "com.mysql.cj.jdbc.Driver";
	private static final String url = "jdbc:mysql://127.0.0.1:3306/his?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
	private static final String user = "root";
	private static final String password = "root";

	static {
		try {
			Class.forName(DriverName);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 活动数据库的链接对象
	 * @return
	 * @throws SQLException
	 */
	public Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, user, password);
	}
	/**
	 * 执行带有占位符的SQL语句，完成查询操作，
	 * @param sql ，带有占位符的查询SQL语句
	 * @param args ，字符串数组，数组元素必须与占位符一一对应，如果为空，表示SQL中没有占位符
	 * @return Result，离线式数据集【Connection，已经关闭】
	 */
	public Result query(String sql, String[] args) {
		Connection connection = null;
		PreparedStatement  statement = null;
		ResultSet  resultSet = null;
		Result result = null;
		try {
			connection =  getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; args!=null &&  i < args.length; i++) {
				statement.setString(i+1, args[i]);
			}
			resultSet =  statement.executeQuery(); // 数据的搬运工
//			在线式转换为离线式
			result = ResultSupport.toResult(resultSet);
			return result;
		} catch (SQLException e) {
			System.out.println(sql); 
			System.out.println( Arrays.toString(args) );
		}finally {
			close(resultSet, statement, connection);			
		}	
		
		return result;
	}
	
	/**
	 * 执行带有占位符的SQL语句，完成查询操作，
	 * @param sql ，带有占位符的查询SQL语句
	 * @param args ，字符串数组，数组元素必须与占位符一一对应，如果为空，表示SQL中没有占位符
	 * @return Result，离线式数据集【Connection，已经关闭】
	 */
	public int  insertAndGetId(String sql, String[] args) {
		Connection connection = null;
		PreparedStatement  statement = null;
		ResultSet  resultSet = null;		
		try {
			connection =  getConnection();
			statement = connection.prepareStatement(sql);
			for (int i = 0; args!=null &&  i < args.length; i++) {
				statement.setString(i+1, args[i]);
			}
			int  i = statement.executeUpdate();
			if (i <=0) {
				return -1;
			}
			resultSet =  statement.executeQuery(" SELECT LAST_INSERT_ID() ");
			if (resultSet.next()) {
				return  resultSet.getInt(1);
			}else {
				return -1;
			}
		 
		} catch (SQLException e) {
			System.out.println(sql); 
			System.out.println( Arrays.toString(args) );
		}finally {
			close(resultSet, statement, connection);			
		}			
		return -1;
	}
	
	
	

	/**
	 * 执行带有占位符的SQL语句，完成增删改操作，
	 * 
	 * @param sql
	 *            ，带有占位符的增删改SQL语句
	 * @param args
	 *            ，字符串数组，数组元素应该与占位符一一对应
	 * @return 返回影响记录的函数，-1 ：失败
	 */
	public int update(String sql, String[] args) {
		int i = -1;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int j = 0; args != null && j < args.length; j++) {
				preparedStatement.setString(j + 1, args[j]);
			}
			i = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			System.out.println("执行失败：" + e.getMessage());
			System.out.println("执行失败：" + sql);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			close(null, preparedStatement, connection);
		}
		return i;
	}

	private void close(ResultSet resultSet, PreparedStatement statement, Connection connection) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

}
